
import tushare as ts
import pandas as pd
from sqlalchemy import create_engine
import datetime
import os
import numpy as np

import sys, time
# from colorama import Fore, Back, Style
# 连接数据库
engine_ts  = create_engine("mysql+pymysql://root:123456@127.0.0.1:3306/yucezhe?charset=utf8mb4",echo=False,max_overflow=5)

import colorama
from colorama import init, Fore, Back, Style
init(autoreset=True)


DB_TABLE_NAME ="tonghuashun_20220110_tmp"

PATH_FILE_DIR="E:/export/1_QFQ/HS_Stock_Day/"
# PATH_FILE_DIR="day_file/"
# 定义样式打印
def printinfo(text):
    print(Fore.CYAN +Style.BRIGHT+ text)

#////////////////////////////////////////////////read_day_data Start /////////////////////////////////////////////////////////////////////////////
def read_data_day(pathFile):

    #firstline = read_fileone(pathFile)
    # # 读取文件第一行数据 以空格为界，分割字符串
    list_firstline = read_fileone(pathFile).split()

    # pathFile = path_file+"SZ#001234.txt"
    # print(list_firstline[0],list_firstline[1],list_firstline[2],list_firstline[3])
    # 定义df头部文件,'pre_close','change'
    titleName = ['trade_date', 'open', 'high', 'low', 'close', 'vol', 'amount']
    df = pd.read_table(pathFile, header=1, encoding="gbk", names=titleName)
    # 删除最后一行SZ#399007.txt 5254    数据来源:通达信   NaN   NaN   NaN    NaN          NaN           NaN  1  1
    df.drop([len(df) - 1], inplace=True)

    # 处理字符串Start SH#xxxxxx.txt
    pathFile = pathFile[-13:]
    mk = pathFile[:2]
    code = pathFile[3:][0:6]
    tscode = code + '.' + mk
    # 在df后插入列
    # 插入ts_code列
    df.insert(0, 'ts_code', tscode, allow_duplicates=False)
    # 显示所有列
    # pd.set_option('display.max_columns', None)
    # # 显示所有行
    # pd.set_option('display.max_rows', None)
    # 设置value的显示长度为100，默认为50
    # pd.set_option('max_colwidth', 10)
    # 显示完整列的信息
    pd.set_option('display.expand_frame_repr', False)
    # print(df)
    # 判断是否有记录
    if (df.shape[0] > 0):

        return df
    else:
        return df[0:0]
#////////////////////////////////////////////////read_day_data End /////////////////////////////////////////////////////////////////////////////
#////////////////////////////////////////////////read_data_week Start/////////////////////////////////////////////////////////////////////////////
def read_data_week(pathFile):

    # pathFile = pathFile +"SZ#000651.txt"
    # print('read_data_week('+pathFile+')')
    titleName = ['trade_date','open', 'high', 'low', 'close', 'vol', 'amount']
    df = pd.read_table(pathFile, header=1, encoding="gbk", names=titleName ,converters={'trade_times':str})
    # 删除最后一行SZ#399007.txt 5254    数据来源:通达信   NaN   NaN   NaN    NaN          NaN           NaN  1  1
    df.drop([len(df) - 1], inplace=True)

    #df["trade_datetime"]= pd.to_datetime(df.trade_date +' '+df.trade_times,format='%Y/%m/%d %H%M')
    df["trade_datetime"] = pd.to_datetime(df.trade_date,format='%Y/%m/%d')
    df.set_index('trade_datetime', inplace=True)
    #df.drop(['trade_times'],axis=1, inplace=True)

    # print(df.shape[0])


    df_week=df.resample('W').agg(
        {
            'trade_date':'last',
            'open':'first',
            'high':'max',
            'low': 'min',
            'close': 'last',
            'vol': 'sum',
            'amount': 'sum'
        }).dropna()
    # print(df_week)
    return df_week

#////////////////////////////////////////////////read_data_week End/////////////////////////////////////////////////////////////////////////////


#////////////////////////////////////////////////read_data_15min Start/////////////////////////////////////////////////////////////////////////////


def read_data_15min(pathFile):
    # pathFile = pathFile +"SZ#000651.txt"
    # print('read_data('+pathFile+')')
    titleName = ['trade_date','trade_times','open', 'high', 'low', 'close', 'vol', 'amount']
    df = pd.read_table(pathFile, header=1, encoding="gbk", names=titleName ,converters={'trade_times':str})
    # 删除最后一行SZ#399007.txt 5254    数据来源:通达信   NaN   NaN   NaN    NaN          NaN           NaN  1  1
    df.drop([len(df) - 1], inplace=True)

    df["trade_datetime"]=pd.to_datetime(df.trade_date +' '+df.trade_times,format='%Y/%m/%d %H%M')
    df.set_index('trade_datetime', inplace=True)
    # df.drop(['trade_date','trade_times'],axis=1, inplace=True)

    df_15m=df.resample('15min',closed='right',label='right').agg(
        {
            'trade_date':'last',
            'trade_times': 'last',
            'open':'first',
            'high':'max',
            'low': 'min',
            'close': 'last',
            'vol': 'sum',
            'amount': 'sum'
        }).dropna()
    pd.set_option('display.expand_frame_repr', False)

    # print(df_15m)
    return df_15m
#////////////////////////////////////////////////read_data_15min End/////////////////////////////////////////////////////////////////////////////
#////////////////////////////////////////////////read_data_60min Start/////////////////////////////////////////////////////////////////////////////
def read_data_60min(pathFile):
    # print(pathFile)
    titleName = ['trade_date','trade_times','open', 'high', 'low', 'close', 'vol', 'amount']
    df = pd.read_table(pathFile, header=1, encoding="gbk", names=titleName ,converters={'trade_times':str})
    # 删除最后一行SZ#399007.txt 5254    数据来源:通达信   NaN   NaN   NaN    NaN          NaN           NaN  1  1
    df.drop([len(df) - 1], inplace=True)

    df["trade_datetime"]=pd.to_datetime(df.trade_date +' '+df.trade_times,format='%Y/%m/%d %H%M')
    df.set_index('trade_datetime', inplace=True)
    # df.drop(['trade_date','trade_times'],axis=1, inplace=True)

    # print('df.shape[0]',df.shape[0])
    # df.drop(df[df.vol <= 0.00].index, inplace=True)

    df_30m=df.resample('30min',closed='right',label='right').agg(
        {
            'trade_date': 'last',
            'trade_times': 'last',
            'open':'first',
            'high':'max',
            'low': 'min',
            'close': 'last',
            'vol': 'sum',
            'amount': 'sum'
        }).dropna()
    # print('df_30m',df_30m.shape[0])
    pd.set_option('display.expand_frame_repr', False)

    #
    if(df_30m.shape[0] % 2 != 0):
        raise Exception("%s 30min data has wrong length. df30:%d" %('',len(df_30m)))

    idx=list(range(0,len(df_30m)//2))*2

    idx.sort()
    df_30m['idx']=idx
    df_30m=df_30m.reset_index()
    # print(df_30m)
    df_60=df_30m.groupby(by='idx').agg(
        {
            'trade_datetime':'last',
            'trade_date': 'last',
            'trade_times': 'last',
            'open': 'first',
            'high': 'max',
            'low': 'min',
            'close': 'last',
            'vol': 'sum',
            'amount': 'sum'
        }
    )
    # # 显示完整列的信息
    # pd.set_option('display.expand_frame_repr', False)
    df_60.set_index("trade_datetime",inplace=True)
    # print(df_30m)
    return df_60

#////////////////////////////////////////////////read_data_15min End/////////////////////////////////////////////////////////////////////////////

#////////////////////////////////////////////////read_data_30min  Start/////////////////////////////////////////////////////////////////////////////
def read_data_30min(pathFile):
    # print("read_data_30min(pathFile)")
    titleName = ['trade_date', 'trade_times', 'open', 'high', 'low', 'close', 'vol', 'amount']
    df = pd.read_table(pathFile, header=1, encoding="gbk", names=titleName, converters={'trade_times': str})
    # 删除最后一行SZ#399007.txt 5254    数据来源:通达信   NaN   NaN   NaN    NaN          NaN           NaN  1  1
    df.drop([len(df) - 1], inplace=True)

    df["trade_datetime"] = pd.to_datetime(df.trade_date + ' ' + df.trade_times, format='%Y/%m/%d %H%M')
    df.set_index('trade_datetime', inplace=True)
    # df.drop(['trade_date','trade_times'],axis=1, inplace=True)

    df_30m = df.resample('30min', closed='right', label='right').agg(
        {
            'trade_date': 'last',
            'trade_times': 'last',
            'open': 'first',
            'high': 'max',
            'low': 'min',
            'close': 'last',
            'vol': 'sum',
            'amount': 'sum'
        }).dropna()
    pd.set_option('display.expand_frame_repr', False)
    # print(df_30m)
    return df_30m
#////////////////////////////////////////////////read_data_30min  End/////////////////////////////////////////////////////////////////////////////
# 获取目录信息 导入文件，导出文件
# path_file 文件路径
# exp_file 导出文件路径
# period 文件周期
def export_file(path_file,exp_file,period):
    pathDir = os.listdir(path_file)
    count = len(pathDir)
    # print(path_file+'文件数量',count)
    start = time.perf_counter()
    c = 0
    scale = count
    i=1
    for file in pathDir:
        path = path_file + file
        # 调用读取文件函数，返回df
        # df =read_day_data(path)
        if period=="WEEK" :
            df =read_data_week(path)
        if period=="DAY" :
            df =read_data_day(path)
        if period=="15MIN" :
            df =read_data_15min(path)
        if period=="60MIN" :
            df =read_data_60min(path)
        if period=="30MIN" :
            df =read_data_30min(path)
        # 写入数据库
        # df.to_sql(DB_TABLE_NAME, engine_ts, index=True, if_exists='append')

        pathFile = path[-13:]
        mk = pathFile[:2]
        code = pathFile[3:][0:6]
        tscode = mk+code
        expfile=exp_file+tscode+"_"+period+".CSV"

        # df.to_csv(expfile,index=False)

        # 进度条 [1]	17 % ▋▋▋▋▋▋▋▋......................................... 0.83s 	SZ#000651.txt
        a = (i / scale) * 100
        b='.'*50
        c='▋'* (int((i*50)/scale))
        b ='.'*(49-int((i*50)/scale))
        dur = time.perf_counter()  # //每次获取当前时间
        print("\r[{}]\033[1;33m\t{:^3.0f}% {}{} {:.2f}s".format(i,a, c, b, dur),'\t'+file,"=>",expfile,"文件数量",count, end=' ')
        # print('')

        # if (i == 1):
        #     # print(df.loc[df.shape[0]-10:df.shape[0]])
        #     break

        i+=1
        # time.sleep(1)

# 读取文件的一行数据
def read_fileone(filename):
    # 打开文件
    fo = open(filename, "r")
    fileFirstLine = fo.readline()
    fo.close()
    return fileFirstLine

# ////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
# Press the green button in the gutter to run the script.
# =============================    程序入口    =============================

import _thread
if __name__ == '__main__':
    # 统计程序开始时间
    starttime = datetime.datetime.now()
    # os.system('cls')
    print('\n=============================\t程序开始运行(StockDataConversion)\t=============================')

    print("INFO:清空原有数据。。。")
    # engine_ts.execute("delete from " + db_tablename)
    # print("\n============================WEEK============================================================")
    # df=export_file("E:/export/1_QFQ/HS_Stock_Day/","e:/dc/","WEEK")
    # print("\n=============================DAY===========================================================")
    # df=export_file("E:/export/1_QFQ/HS_Stock_Day/", "e:/dc/", "DAY")
    # print("\n===================15MIN=====================================================================")
    # df=export_file("E:/export/1_QFQ/HS_Stock_5MIN/", "e:/dc/", "15MIN")

    #df = export_file("day_file/", "e:/dc/", "30MIN")
    # print("\n====================30MIN====================================================================")
    # df=export_file("E:/export/1_QFQ/HS_Stock_5MIN/", "e:/dc/", "30MIN")

    # print("\n====================60MIN====================================================================")
    # df = export_file("E:/export/1_QFQ/HS_Stock_5MIN/", "e:/dc/", "60MIN")
    # # df = export_file("day_file/", "e:/dc/", "60MIN")
    #
    # df=export_file("E:/export/1_QFQ/HS_Stock_5MIN/", "e:/dc/", "30MIN")
    # print(df)

    #
    print("\n====================60MIN====================================================================")
    df = export_file("E:\\stcokData\\20220116\\export\\stock_5MIN_qfq\\", "e:/dc/", "60MIN")

    print("\n====================15MIN====================================================================")
    df = export_file("E:\\stcokData\\20220116\\export\\stock_5MIN_qfq\\", "e:/dc/", "15MIN")

    print("\n====================30MIN====================================================================")
    df = export_file("E:\\stcokData\\20220116\\export\\stock_5MIN_qfq\\", "e:/dc/", "30MIN")
    # END 程序结束



    # 统计程序开结束时间
    endtime = datetime.datetime.now()
    printinfo("\n程序运行时间：" + str(endtime - starttime))
    print('=============================\t程序运行结束\t=============================')

# =============================    程序入口    =============================